
if exists (select * from sys.objects where object_id = OBJECT_ID(N'[dbo].[cms_udf_SplitString]') and type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function [dbo].[cms_udf_SplitString]
go

create function [dbo].[cms_udf_SplitString]
(
    @string varchar(8000),
    @delimiter varchar(5)
)
returns @strings table
(
    part nvarchar(50)
)
as
begin
	declare @splitLength INT
	    
	while len(@string) > 0
	begin
		select @splitLength =
			case charindex(@delimiter, @string)
				when 0 then len(@string)
					else charindex(@delimiter, @string) - 1
			end
	 
		insert into @strings 
		select substring(@string, 1 ,@splitLength)
			where not exists (select * FROM @strings where part = substring(@string, 1 ,@splitLength))
	    
		select @string = 
			case (len(@string) - @splitLength)
				when 0 then  ''
					else right(@string, len(@string) - @splitLength - 1)
			end 	
	end
	return  
end

go


